import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_mov_head_router = APIRouter(prefix="/material/movHead", tags=["物料调拨管理"],
                                 dependencies=[Depends(oauth2_scheme)])


class MaterialTransferSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("", description="调拨开始日期(yyyyMMdd)")
    ftakedateEnd: Optional[str] = Field("", description="调拨结束日期(yyyyMMdd)")
    fmovuser: Optional[str] = Field("", description="调拨人")
    fstocknoOut: Optional[str] = Field("", description="调出仓库编号")
    foutpointNo2: Optional[str] = Field("", description="调出点位编号")
    fbillno: Optional[str] = Field("", description="单据编号(支持前缀匹配)")
    fbillstate: Optional[str] = Field("", description="单据状态编码")


@data_mov_head_router.get("/list", summary="物料调拨查询接口")
def list_mov_head(search: MaterialTransferSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_tc_mov_head where 1=1 "
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%') "
        param["fbillno"] = search.fbillno
    if search.fmovuser != "":
        sql = sql + " and fmovuser = :fmovuser "
        param["fmovuser"] = search.fmovuser
    if search.fstocknoOut != "":
        sql = sql + " and fstockno_out = :fstocknoOut "
        param["fstocknoOut"] = search.fstocknoOut
    if search.foutpointNo2 != "":
        sql = sql + " and foutpoint_no2 = :foutpointNo2 "
        param["foutpointNo2"] = search.foutpointNo2
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fmovdate,'yyyymmdd') between :ftakedateStart  and  :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fmovuser,fmovdate,fstockno_out,foutpoint_no2,fitemcount,fpiececount,"
    sql = sql + " ftotalamt,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate"
    sql = sql + " from i_tc_mov_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%') "
        param["fbillno"] = search.fbillno
    if search.fmovuser != "":
        sql = sql + " and fmovuser = :fmovuser "
        param["fmovuser"] = search.fmovuser
    if search.fstocknoOut != "":
        sql = sql + " and fstockno_out = :fstocknoOut "
        param["fstocknoOut"] = search.fstocknoOut
    if search.foutpointNo2 != "":
        sql = sql + " and foutpoint_no2 = :foutpointNo2 "
        param["foutpointNo2"] = search.foutpointNo2
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fmovdate,'yyyymmdd') between :ftakedateStart  and  :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    if search.order == "desc":
        sql = sql + " order by  fmovdate,fbillno desc "
    else:
        sql = sql + " order by  fmovdate,fbillno "
    sql = sql + " limit :limit offset :offset "
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fgoodno,b.fbuyprice,b.fqty,b.ftotamt,b.fmemo,b.fprocdate,c.fgoodname"
    sql_3 = sql_3 + " from i_tc_mov_head a,i_tc_mov_detail b,i_ts_good_info c "
    sql_3 = sql_3 + " where a.fbillno=b.fbillno"
    sql_3 = sql_3 + " and b.fgoodno=c.fgoodno"
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%') "
        param["fbillno"] = search.fbillno
    if search.fmovuser != "":
        sql_3 = sql_3 + " and a.fmovuser = :fmovuser "
        param["fmovuser"] = search.fmovuser
    if search.fstocknoOut != "":
        sql_3 = sql_3 + " and fstockno_out = :fstocknoOut "
        param["fstocknoOut"] = search.fstocknoOut
    if search.foutpointNo2 != "":
        sql_3 = sql_3 + " and foutpoint_no2 = :foutpointNo2 "
        param["foutpointNo2"] = search.foutpointNo2
    if search.fbillstate != "":
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(a.fmovdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        fmovdate = ""
        try:
            fmovdate = data[key][3].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fmovdate == ""):
            fmovdate = None
        fupddate = ""
        try:
            fupddate = data[key][10].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)
        if (fupddate == ""):
            fupddate = None
        fcheckdate = ""
        try:
            fcheckdate = data[key][12].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)
        if (fcheckdate == ""):
            fcheckdate = None

        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fmovuser'] = data[key][2]
        d1['fmovdate'] = fmovdate
        d1['fstocknoOut'] = data[key][4]
        d1['foutpointNo2'] = data[key][5]
        d1['fitemcount'] = data[key][6]
        d1['fpiececount'] = data[key][7]
        d1['ftotalamt'] = data[key][8]
        d1['fexplanation'] = data[key][9]
        d1['fupddate'] = fupddate
        d1['foperator'] = data[key][11]
        d1['fcheckdate'] = fcheckdate
        d1['fcheckuser'] = data[key][13]
        d1['fbillstate'] = data[key][14]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fgoodno'] = data_detail[j1][2]
                d2['fbuyprice'] = data_detail[j1][3]
                d2['fqty'] = data_detail[j1][4]
                d2['ftotamt'] = data_detail[j1][5]
                d2['fmemo'] = data_detail[j1][6]
                d2['fprocdate'] = data_detail[j1][7]
                d2['fgoodname'] = data_detail[j1][8]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)
    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class MaterialTransferDetail(BaseModel):
    """物料调拨明细"""
    fline: int = Field(..., description="明细行号")
    fgoodno: str = Field(..., description="物料编号")
    fgoodname: str = Field(..., description="物料名称")
    fbuyprice: float = Field(..., description="调拨单价")
    fqty: float = Field(..., description="调拨数量")
    ftotamt: float = Field(..., description="调拨金额")
    fmemo: str = Field(..., description="备注")
    fprocdate: Optional[str] = Field(None, description="处理日期")


class MaterialTransferCreate(BaseModel):
    """物料调拨新增请求参数"""
    fbillno: str = Field(..., description="单据编号")
    fmovdate: str = Field(..., description="调拨日期(yyyy-MM-dd)")
    fmovuser: str = Field(..., description="调拨人")
    fsiteNo: str = Field(..., description="站点编号")
    fstocknoOut: str = Field(..., description="调出仓库编号")
    foutpointNo2: str = Field(..., description="调出点位编号")
    fexplanation: Optional[str] = Field("", description="调拨说明")
    details: List[MaterialTransferDetail] = Field(..., description="调拨明细列表")


@data_mov_head_router.post("/create", summary="物料调拨新增接口")
def create_mov_head(create: MaterialTransferCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = create.fbillno
        # 返回内容
        d1 = {}
        detail_list = []
        with db.begin():
            jsondata = create.details
            for data in jsondata:
                sql = "insert into i_tc_mov_detail(fbillno,fline,fgoodno,fbuyprice,fqty,ftotamt,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fgoodno , :fbuyprice , :fqty , :ftotamt , :fprocdate , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fgoodno": data.fgoodno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fprocdate": data.fprocdate,
                    "fmemo": data.fmemo
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + float(data.fqty)
                ftotalamt = ftotalamt + float(data.ftotamt)

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fgoodno'] = data.fgoodno
                d2['fgoodname'] = data.fgoodname
                d2['fbuyprice'] = float(data.fbuyprice)
                d2['fqty'] = float(data.fqty)
                d2['ftotamt'] = float(data.ftotamt)
                d2['fmemo'] = data.fmemo
                d2['fprocdate'] = data.fprocdate
                detail_list.append(d2)
            sql = "insert into i_tc_mov_head(fbillno,fsite_no,fmovuser,fmovdate,fstockno_out,foutpoint_no2,fitemcount,fpiececount,ftotalamt,fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values( :fbillno , :fsiteNo , :fmovuser , :fmovdate , :fstocknoOut , :foutpointNo2 , :fitemcount , :fpiececount , :ftotalamt , :fexplanation ,now(), :fmovuser ,1,1)"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": create.fsiteNo,
                "fmovuser": create.fmovuser,
                "fmovdate": create.fmovdate,
                "fstocknoOut": create.fstocknoOut,
                "foutpointNo2": create.foutpointNo2,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": create.fexplanation,
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = create.fsiteNo
            d1['fmovuser'] = create.fmovuser
            d1['fmovdate'] = create.fmovdate
            d1['fstocknoOut'] = create.fstocknoOut
            d1['foutpointNo2'] = create.foutpointNo2
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = ""
            d1['fupddate'] = create.fmovdate
            d1['foperator'] = create.fmovuser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1['fbilltype'] = 1
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_mov_head_router.post("/update", summary="物料调拨修改接口")
def update_mov_head(update: MaterialTransferCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = update.fbillno
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "delete from i_tc_mov_detail where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
            })
            jsondata = update.details
            for data in jsondata:
                sql = "insert into i_tc_mov_detail(fbillno,fline,fgoodno,fbuyprice,fqty,ftotamt,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fgoodno , :fbuyprice , :fqty , :ftotamt , :fprocdate , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fgoodno": data.fgoodno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fprocdate": data.fprocdate,
                    "fmemo": data.fmemo
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + float(data.fqty)
                ftotalamt = ftotalamt + float(data.ftotamt)
            sql = "update i_tc_mov_head "
            sql = sql + " set fstockno_out= :fstocknoout ,foutpoint_no2= :foutpointno2 ,fexplanation= :fexplanation "
            sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "
            db.execute(text(sql), {
                "fstocknoout": update.fstocknoOut,
                "foutpointno2": update.foutpointNo2,
                "fexplanation": update.fexplanation,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "userid": user_id,
                "fbillno": fbillno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_mov_head_router.post("/delete/{fbillno}", summary="物料调拨删除接口")
def delete_mov_head(fbillno: str = Path(description="单号"), db: Session = Depends(get_db),
                    token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            param = {"fbillno": fbillno}
            sql = "select count(1) from i_tc_mov_head where fbillno= :fbillno  and fbillstate>1"
            data = db.execute(text(sql), param).fetchall()
            if data[0][0] > 0:
                return BaseResponse.error(message="该订单不能删除")
            else:
                sql = "delete from i_tc_mov_detail where fbillno= :fbillno "
                db.execute(text(sql), param)
                sql = "delete from i_tc_mov_head where fbillno= :fbillno "
                db.execute(text(sql), param)
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
